package fact.view.model;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import javax.annotation.Resource;
import javax.faces.bean.ManagedBean;
import javax.faces.bean.RequestScoped;
import javax.faces.bean.ViewScoped;
import javax.sql.DataSource;

import fact.view.DTO.SegmentDTO;


@ManagedBean(name = "segmentModelBean")
//@RequestScoped
@ViewScoped
public class SegmentModelBean {
	
	@Resource(name="jdbc/factentry")
	private DataSource ds;
	
	public ArrayList<SegmentDTO> getSegmentList() throws SQLException
	{
		ArrayList<SegmentDTO> segmentList=new ArrayList<SegmentDTO>();
		if(ds==null)
			throw new SQLException("Can't get data source");
		Connection con = ds.getConnection();
 
		if(con==null)
			throw new SQLException("Can't get database connection");
 
		PreparedStatement ps = con.prepareStatement("Select * from MST_SEGMENTS Order by POSITION,SEGMENT_NAME");  
		ResultSet result =  ps.executeQuery();
		SegmentDTO s=null;
				while(result.next())
				{
					s=new SegmentDTO();
					s.setSegmentId(result.getInt("SEGMENT_ID"));
					s.setSegmentName(result.getString("SEGMENT_NAME"));
					s.setPosition(result.getInt("POSITION"));
					
					segmentList.add(s);
				}
		
		
		return segmentList;
	}
	
	public SegmentDTO getSegment(String segmentName) throws SQLException
	{
		if(ds==null)
			throw new SQLException("Can't get data source");
		Connection con = ds.getConnection();
 
		if(con==null)
			throw new SQLException("Can't get database connection");
 
		PreparedStatement ps = con.prepareStatement("Select * from MST_SEGMENTS Where upper(SEGMENT_NAME)=upper(?)");  
		ps.setString(1, segmentName);
		ResultSet result =  ps.executeQuery();
		SegmentDTO s=null;
				if(result.next())
				{
					s=new SegmentDTO();
					s.setSegmentId(result.getInt("SEGMENT_ID"));
					s.setSegmentName(result.getString("SEGMENT_NAME"));
					s.setPosition(result.getInt("POSITION"));
					
				}
		
		return s;
	}
	
	public SegmentDTO getSegment(int segmentId) throws SQLException
	{
		if(ds==null)
			throw new SQLException("Can't get data source");
		Connection con = ds.getConnection();
 
		if(con==null)
			throw new SQLException("Can't get database connection");
 
		PreparedStatement ps = con.prepareStatement("Select * from MST_SEGMENTS Where SEGMENT_ID=?");  
		ps.setInt(1, segmentId);
		ResultSet result =  ps.executeQuery();
		SegmentDTO s=null;
				if(result.next())
				{
					s=new SegmentDTO();
					s.setSegmentId(result.getInt("SEGMENT_ID"));
					s.setSegmentName(result.getString("SEGMENT_NAME"));
					s.setPosition(result.getInt("POSITION"));				
				}
		
		
		return s;
	}
	
	
	public boolean addSegment(String segmentName,int position) throws SQLException
	{
		if(ds==null)
			throw new SQLException("Can't get data source");
		Connection con = ds.getConnection();
 
		if(con==null)
			throw new SQLException("Can't get database connection");
 
		PreparedStatement ps = con.prepareStatement("Insert into MST_SEGMENTS Values(SQN_SEGMENT.NEXTVAL,?,?)");  
		ps.setString(1, segmentName);
		ps.setInt(2, position);
		int result =  ps.executeUpdate();
		
		if(result==1)
			return true;
		else
			return false;
		
	}
	
	public boolean deleteSegment(int segmentId) throws SQLException
	{
		if(ds==null)
			throw new SQLException("Can't get data source");
		Connection con = ds.getConnection();
 
		if(con==null)
			throw new SQLException("Can't get database connection");
 
		PreparedStatement ps = con.prepareStatement("Delete MST_SEGMENTS Where SEGMENT_ID=?");  
		ps.setInt(1, segmentId);
		int result =  ps.executeUpdate();
		
		if(result==1)
			return true;
		else
			return false;
		
	}
	

	public boolean editSegment(String segmentName,int segmentPosition,int segmentId) throws SQLException
	{
		if(ds==null)
			throw new SQLException("Can't get data source");
		Connection con = ds.getConnection();
 
		if(con==null)
			throw new SQLException("Can't get database connection");
 
		PreparedStatement ps = con.prepareStatement("Update MST_SEGMENTS Set SEGMENT_NAME=?,POSITION=? Where SEGMENT_ID=?");  
		ps.setString(1, segmentName);
		ps.setInt(2, segmentPosition);
		ps.setInt(3, segmentId);
		int result =  ps.executeUpdate();
		
		if(result==1)
			return true;
		else
			return false;
		
	}
		

}